import os
import sqlite3
import re
import datetime
import sys

def import_projects():
    """
    从SQL文件导入项目数据到SQLite数据库
    """
    print("开始导入项目数据...")
    
    db_dir = r"C:\military_engineering_db"
    db_path = os.path.join(db_dir, "military_engineering.db")
    sql_file_path = "import_projects.sql"
    
    # 检查SQL文件是否存在
    if not os.path.exists(sql_file_path):
        print(f"SQL文件不存在: {sql_file_path}")
        print(f"当前工作目录: {os.getcwd()}")
        print(f"目录内容: {os.listdir('.')}")
        return False
    
    # 检查数据库文件是否存在
    if not os.path.exists(db_path):
        print(f"数据库文件不存在: {db_path}")
        return False
    
    try:
        # 读取SQL文件内容
        print(f"正在读取SQL文件: {sql_file_path}")
        with open(sql_file_path, 'r', encoding='utf-8-sig') as f:
            sql_content = f.read()
        
        print(f"SQL文件大小: {len(sql_content)} 字节")
        
        # 分割SQL语句
        # 使用正则表达式匹配INSERT语句，处理可能包含分号的字符串值
        sql_statements = []
        pattern = r'INSERT INTO projects \([^;]+\) VALUES \([^;]+\);'
        matches = re.finditer(pattern, sql_content, re.DOTALL)
        
        for match in matches:
            sql_statements.append(match.group(0))
        
        print(f"共解析出 {len(sql_statements)} 条SQL语句")
        
        if len(sql_statements) == 0:
            print("没有找到有效的SQL语句，请检查SQL文件格式")
            print(f"SQL文件内容预览: {sql_content[:500]}...")
            return False
        
        # 连接数据库
        print(f"正在连接数据库: {db_path}")
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # 开始事务
        conn.execute("BEGIN TRANSACTION")
        
        # 执行SQL语句
        success_count = 0
        error_count = 0
        
        print("正在导入数据...")
        for i, sql in enumerate(sql_statements):
            try:
                # 将MySQL风格的INSERT语句转换为SQLite兼容的格式
                # 替换MySQL特有的语法
                sql = sql.replace("\\n", "\n")  # 处理换行符
                
                # 确保SQL语句包含created_at和updated_at字段
                if "created_at" not in sql or "updated_at" not in sql:
                    # 如果没有这些字段，添加它们
                    now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                    sql = sql.replace(");", f", '{now}', '{now}');")
                
                # 执行SQL语句
                cursor.execute(sql)
                success_count += 1
                
                # 每100条语句输出一次进度
                if (i + 1) % 100 == 0 or i == len(sql_statements) - 1:
                    print(f"已处理 {i + 1}/{len(sql_statements)} 条语句")
                
            except Exception as e:
                error_count += 1
                print(f"执行第 {i + 1} 条SQL语句时出错: {str(e)}")
                print(f"错误的SQL语句: {sql[:100]}...")  # 只显示前100个字符
        
        # 提交事务
        conn.commit()
        
        # 创建里程碑节点
        print("正在为项目创建里程碑节点...")
        create_milestones_for_projects(conn)
        
        # 关闭连接
        cursor.close()
        conn.close()
        
        print(f"数据导入完成！成功: {success_count}, 失败: {error_count}")
        return True
        
    except Exception as e:
        print(f"导入数据时出错: {str(e)}")
        import traceback
        traceback.print_exc()
        return False

def create_milestones_for_projects(conn):
    """
    为所有项目创建默认的里程碑节点
    """
    try:
        cursor = conn.cursor()
        
        # 获取所有项目
        cursor.execute("SELECT id FROM projects")
        projects = cursor.fetchall()
        
        if not projects:
            print("没有找到任何项目，跳过创建里程碑节点")
            return False
        
        print(f"找到 {len(projects)} 个项目")
        
        # 获取当前时间
        now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        # 获取默认里程碑节点
        cursor.execute("SELECT value FROM settings WHERE key = 'default_milestones_early'")
        early_milestones = cursor.fetchone()[0].split(',')
        
        cursor.execute("SELECT value FROM settings WHERE key = 'default_milestones_bidding'")
        bidding_milestones = cursor.fetchone()[0].split(',')
        
        cursor.execute("SELECT value FROM settings WHERE key = 'default_milestones_construction'")
        construction_milestones = cursor.fetchone()[0].split(',')
        
        cursor.execute("SELECT value FROM settings WHERE key = 'default_milestones_completion'")
        completion_milestones = cursor.fetchone()[0].split(',')
        
        print(f"前期阶段里程碑: {early_milestones}")
        print(f"发包阶段里程碑: {bidding_milestones}")
        print(f"施工阶段里程碑: {construction_milestones}")
        print(f"竣工阶段里程碑: {completion_milestones}")
        
        # 为每个项目创建里程碑节点
        milestone_count = 0
        for project in projects:
            project_id = project[0]
            
            # 创建前期阶段里程碑
            for milestone in early_milestones:
                cursor.execute('''
                INSERT INTO milestones (project_id, name, category, is_custom, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?)
                ''', (project_id, milestone, '前期', 0, now, now))
                milestone_count += 1
            
            # 创建发包阶段里程碑
            for milestone in bidding_milestones:
                cursor.execute('''
                INSERT INTO milestones (project_id, name, category, is_custom, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?)
                ''', (project_id, milestone, '发包', 0, now, now))
                milestone_count += 1
            
            # 创建施工阶段里程碑
            for milestone in construction_milestones:
                cursor.execute('''
                INSERT INTO milestones (project_id, name, category, is_custom, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?)
                ''', (project_id, milestone, '施工', 0, now, now))
                milestone_count += 1
            
            # 创建竣工阶段里程碑
            for milestone in completion_milestones:
                cursor.execute('''
                INSERT INTO milestones (project_id, name, category, is_custom, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?)
                ''', (project_id, milestone, '竣工', 0, now, now))
                milestone_count += 1
        
        # 提交更改
        conn.commit()
        print(f"已为 {len(projects)} 个项目创建 {milestone_count} 个里程碑节点")
        return True
        
    except Exception as e:
        print(f"创建里程碑节点时出错: {str(e)}")
        import traceback
        traceback.print_exc()
        return False

def convert_mysql_to_sqlite(sql):
    """
    将MySQL风格的SQL语句转换为SQLite兼容的格式
    """
    # 替换MySQL特有的语法
    # 例如：将 `column_name` 转换为 "column_name"
    sql = re.sub(r'`([^`]+)`', r'"\1"', sql)
    
    # 处理其他可能的不兼容问题
    # ...
    
    return sql

if __name__ == "__main__":
    print("正在执行导入数据脚本...")
    success = import_projects()
    if success:
        print("数据导入成功！")
        sys.exit(0)
    else:
        print("数据导入失败！")
        sys.exit(1) 